package com.lidastudio.dbdatamock.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.springframework.stereotype.Service;

import com.lidastudio.dbdatamock.dto.TableInfo;
import com.lidastudio.dbdatamock.service.IDatabaseService;
import com.zaxxer.hikari.HikariDataSource;

import net.andreinc.mockneat.MockNeat;

@Service
public class DatabaseServiceImpl implements IDatabaseService{
	private static final String BASIC_ISO_DATE = "yyyy-MM-dd HH:mm:ss";

	@Override
	public TableInfo getTableMetadata(HikariDataSource ds, String databaseName, String tableName) throws Exception {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			ResultSet columns = databaseMetaData.getColumns(null,null, tableName, null);
			
			TableInfo tableInfo = new TableInfo(tableName);
			List<TableInfo.Column> columnList = new ArrayList<>();
			while(columns.next()) {
			    String columnName = columns.getString("COLUMN_NAME");
			    String columnSize = columns.getString("COLUMN_SIZE");
			    int datatype = columns.getInt("DATA_TYPE");
			    String isNullable = columns.getString("IS_NULLABLE");
			    String isAutoIncrement = columns.getString("IS_AUTOINCREMENT");
			    
			    TableInfo.Column column = new TableInfo.Column(columnName,datatype,columnSize,isNullable,isAutoIncrement);
			    columnList.add(column);
			}
			
			if( columnList == null || columnList.size() == 0 ) {
				throw new Exception("There is no columns in table "+ tableName);
			}
			
			TableInfo.Column[] columnAry = new TableInfo.Column[columnList.size()];
			columnAry = columnList.toArray(columnAry);
			tableInfo.setColumns(columnAry);
			return tableInfo;
		}finally {
			if( rs != null ) {
				rs.close();
			}
			if( preparedStatement != null ) {
				preparedStatement.close();
			}
		}
	}

	@Override
	public void mock(HikariDataSource ds, String databaseName, String tableName, Long mockCount, Integer batchSize)
			throws Exception {
		TableInfo tableInfo = getTableMetadata(ds, databaseName, tableName);

		if( tableInfo == null ) {
			throw new Exception("Failed to get table information.");
		}else if( tableInfo.getColumns() == null || tableInfo.getColumns().length == 0 ) {
			throw new Exception("There is no columns in table " + tableName);
		}
		
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		
		try {
			MockNeat mockNeat = MockNeat.secure();
			
			conn = ds.getConnection();
			
			List<String> columnNameList = new ArrayList<>();
			List<String> valuePlaceholdersList = new ArrayList<>();
			
			Arrays.asList(tableInfo.getColumns()).stream().forEach((column)->{
				columnNameList.add(column.name);
				valuePlaceholdersList.add("?");
			});
			
			String sql = "insert into " + tableName + "(" + String.join(",", columnNameList) + ")" + " values(" + String.join(",", valuePlaceholdersList) + ")";
			preparedStatement = conn.prepareStatement(sql);
			
			for( long mockIndex = 0 ; mockIndex < mockCount; mockIndex++ ) {
				for( int columnIndex = 0 ; columnIndex < tableInfo.getColumns().length ; columnIndex ++ ) {
					TableInfo.Column column = tableInfo.getColumns()[columnIndex];
					Integer dataType = column.dataType;
					if( column.name.toLowerCase().equals("id") ) {
						preparedStatement.setInt(columnIndex+1, mockNeat.intSeq().increment(1).get());
					}else if( column.name.toLowerCase().contains("ip") && dataType.equals(Types.VARCHAR) ) {
						preparedStatement.setString(columnIndex+1, mockNeat.ipv4s().get());
					}else if( dataType.equals(Types.VARCHAR) ) {
						preparedStatement.setString(columnIndex+1, mockNeat.strings().get());
					}else if( dataType.equals(Types.INTEGER) || dataType.equals(Types.TINYINT) ) {
						preparedStatement.setInt(columnIndex+1, mockNeat.ints().get());
					}else if( dataType.equals(Types.BIGINT) ){
						preparedStatement.setLong(columnIndex+1, mockNeat.longs().get());
					} if( dataType.equals(Types.TIME) || dataType.equals(Types.TIMESTAMP) || dataType.equals(Types.DATE) ) {
						preparedStatement.setDate(columnIndex+1, new java.sql.Date(mockNeat.localDates().thisYear().mapToDate().get().getTime()));
					}else if( dataType.equals(Types.DECIMAL) || dataType.equals(Types.DOUBLE)) {
						preparedStatement.setDouble(columnIndex+1, mockNeat.doubles().get());
					}else if( dataType.equals(Types.FLOAT) ) {
						preparedStatement.setDouble(columnIndex+1, mockNeat.floats().get());
					}
				}
				
				preparedStatement.addBatch();
				
				// execute batch every batchSize records
				if( (mockIndex > 0 && (mockIndex+1) % batchSize == 0) ) {
					preparedStatement.executeBatch();
				}
			}
			
			if( mockCount % batchSize > 0 ) {
				preparedStatement.executeBatch();
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally {
			if( rs != null ) {
				rs.close();
			}
			if( preparedStatement != null ) {
				preparedStatement.close();
			}
		}
	}

}
